﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;

namespace SwenAssignment3.Classes
{
    public class DRHotelDBManager
    {
        #region User Creation Module Starts
        public static bool Login(string username, string password)
        {
            bool successful = false;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAcc where Username =@username and Password = @password";
                comm.Parameters.AddWithValue("@username", username);
                comm.Parameters.AddWithValue("@password", password);
                //3. execute sql statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process
                if (dr.Read())
                {
                    successful = true;
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return successful;
        }

        public static bool forgetPassword(string username, string question, string answer)
        {
            bool result = false;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAcc where username = @username and SQ = @question and SA = @answer";
                comm.Parameters.AddWithValue("@username", username);
                comm.Parameters.AddWithValue("@question", question);
                comm.Parameters.AddWithValue("@answer", answer);

                //3. execute sql statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process
                if (dr.Read())
                {
                    result = true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static int createNewUser(UserAcc m)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO UserAcc(username,password,staffID,accessRights,SQ,SA) VALUES (@username,@password,@staffID,@accessRights,@SQ,@SA)";

                //3. Bind values to SQL Parameters
                comm.Parameters.AddWithValue("@username", m.Username);
                comm.Parameters.AddWithValue("@password", m.Password);
                comm.Parameters.AddWithValue("@staffID", m.StaffID);
                comm.Parameters.AddWithValue("@accessRights", m.AccessRights);
                comm.Parameters.AddWithValue("@SQ", m.SQ);
                comm.Parameters.AddWithValue("@SA", m.SA);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool searchUserByUsername(string Username)
        {
            string result = "";
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAcc where username = @username";
                comm.Parameters.AddWithValue("@username", Username);

                //3. execute sql statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process
                if (dr.Read())
                {
                    result = (string)dr["id"];
                }

                if (Username == result)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        public static bool searchUserByStaffID(string StaffID)
        {
            string result = "";
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAcc where staffID = @staffID";
                comm.Parameters.AddWithValue("@staffID", StaffID);

                //3. execute sql statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process
                if (dr.Read())
                {
                    result = (string)dr["id"];
                }

                if (StaffID == result)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        public static string retrieveAccessRightsByUsername(string username)
        {
            string result = "";
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT AccessRights FROM UserAcc WHERE Username = @Username";
                comm.Parameters.AddWithValue("@Username", username);

                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process result
                while (dr.Read())
                {
                    result = (string)dr["AccessRights"];
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static UserAcc retrieveUser(string username)
        {
            UserAcc result = new UserAcc();
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAcc where username = @username";
                comm.Parameters.AddWithValue("@username", username);

                //3. execute sql statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process
                if (dr.Read())
                {
                    result.Username = (string)dr["Username"];
                    result.Password = (string)dr["Password"];
                    result.AccessRights = (string)dr["AccessRights"];
                    result.SQ = (string)dr["SQ"];
                    result.SA = (string)dr["SA"];
                    result.StaffID = (string)dr["Staffid"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        // getAllUser
        public static ArrayList retrieveAllUser()
        {
            ArrayList result = new ArrayList();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAcc";
                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();
                //4. Fetch and process result
                while (dr.Read())
                {
                    UserAcc user = new UserAcc();
                    user.Username = (string)dr["username"];
                    user.Password = (string)dr["password"];
                    user.AccessRights = (string)dr["accessRights"];
                    user.StaffID = (string)dr["staffID"];
                    user.SA = (string)dr["SA"];
                    user.SQ = (string)dr["SQ"];
                    result.Add(user);



                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static int updateUserByUsername(UserAcc m)
        {
            int rowUpdated = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE UserAcc SET staff ID= @staffID,Password= @password,AccessRights =@accessRights ,Secret Question =@SQ,Secret Answer= @SA where Username =@username";


                //3. execute sql statement
                comm.Parameters.AddWithValue("@username", m.Username);
                comm.Parameters.AddWithValue("@password", m.Password);
                comm.Parameters.AddWithValue("@accessRights", m.AccessRights);
                comm.Parameters.AddWithValue("@SQ", m.SQ);
                comm.Parameters.AddWithValue("@SA", m.SA);
                comm.Parameters.AddWithValue("@staffID", m.StaffID);

                //4. Execute SQL Statement
                rowUpdated = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowUpdated;
        }

        public static int deleteUser(UserAcc m)
        {
            int rowDeleted = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE INTO UserAcc where username = @username";

                //3. Bind values to SQL Parameters

                comm.Parameters.AddWithValue("@username", m.Username);


                //4. Execute SQL Statement
                rowDeleted = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowDeleted;
        }
        #endregion

        #region Room Availability Cum Booking Module Start

        #region Room DB Manager | Room Details
        public static ArrayList retrieveAllRoomType() 
        {
            ArrayList result = new ArrayList();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT DISTINCT RoomType FROM Rooms";
                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();
                //4. Fetch and process result
                while (dr.Read())
                {
                    string roomtype = (string)dr["RoomType"];
                    result.Add(roomtype);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //Create New Room
        public static int createNewRooms(Rooms r)
        {
            int result = 0;
            try 
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Rooms(RoomNo,Floor,RoomType,BedType,SmokingOrNot,MaxOccupancy,RoomRates,RoomStatus,MaintainedBy) VALUES (@RoomNo,@Floor,@RoomType,@BedType,@SmokingOrNot,@MaxOccupancy,@RoomRates,@RoomStatus,@MaintainedBy)";

                //3. Bind values to SQL Parameters
                comm.Parameters.AddWithValue("@RoomNo", r.RoomNo);
                comm.Parameters.AddWithValue("@Floor", r.Floor);
                comm.Parameters.AddWithValue("@RoomType", r.RoomType);
                comm.Parameters.AddWithValue("@BedType", r.BedType);
                comm.Parameters.AddWithValue("@SmokingOrNot", r.SmokingOrNot);
                comm.Parameters.AddWithValue("@MaxOccupancy", r.MaxOccupancy);
                comm.Parameters.AddWithValue("@RoomRates", r.RoomRates);
                comm.Parameters.AddWithValue("@RoomStatus", r.RoomStatus);
                comm.Parameters.AddWithValue("@MaintainedBy", r.MaintainedBy);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch(SqlException ex)
            {
                throw ex;
            }
            return result;
        }
        
        // Update Room by Room number
        public static int updateRoomByRoomNo(Rooms r)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Rooms SET Floor = @Floor, RoomType = @RoomType, BedType = @BedType, SmokingOrNot = @SmokingOrNot, MaxOccupancy = @MaxOccupancy, RoomRates = @RoomRates, RoomStatus = @RoomStatus, MaintainedBy = @MaintainedBy WHERE RoomNo = @RoomNo";

                //3. Bind values to SQL Parameters
                comm.Parameters.AddWithValue("@RoomNo", r.RoomNo);
                comm.Parameters.AddWithValue("@Floor", r.Floor);
                comm.Parameters.AddWithValue("@RoomType", r.RoomType);
                comm.Parameters.AddWithValue("@BedType", r.BedType);
                comm.Parameters.AddWithValue("@StaffID", r.SmokingOrNot);
                comm.Parameters.AddWithValue("@NoOfAdults", r.MaxOccupancy);
                comm.Parameters.AddWithValue("@NoOfChild", r.RoomRates);
                comm.Parameters.AddWithValue("@AdditionalReq", r.RoomStatus);
                comm.Parameters.AddWithValue("@LateCheckOut", r.MaintainedBy);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //Retrieve all Rooms
        public static ArrayList retrieveAllRooms()
        {
            ArrayList result = new ArrayList();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Rooms";

                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process result
                while (dr.Read())
                {
                    Rooms r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.Floor = (int)dr["Floor"];
                    r.RoomType = (string)dr["RoomType"];
                    r.BedType = (string)dr["BedType"];
                    r.SmokingOrNot = (string)dr["SmokingOrNot"];
                    r.MaxOccupancy = (int)dr["MaxOccupancy"];
                    r.RoomRates = (decimal)dr["RoomRates"];
                    r.RoomStatus = (string)dr["RoomStatus"];
                    r.MaintainedBy = (string)dr["MaintainedBy"];
                    result.Add(r);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //Retrieve all Rooms by roomType and Smoking or Not
        public static ArrayList retrieveAllRoomsByDetail(string RoomType,string SmokingOrNot)
        {
            ArrayList result = new ArrayList();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Rooms WHERE RoomType = @RoomType AND SmokingOrNot = @SmokingOrNot";
                comm.Parameters.AddWithValue("@RoomType", RoomType);
                comm.Parameters.AddWithValue("@SmokingOrNot", SmokingOrNot);

                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process result
                while (dr.Read())
                {
                    Rooms r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.Floor = (int)dr["Floor"];
                    r.RoomType = (string)dr["RoomType"];
                    r.BedType = (string)dr["BedType"];
                    r.SmokingOrNot = (string)dr["SmokingOrNot"];
                    r.MaxOccupancy = (int)dr["MaxOccupancy"];
                    r.RoomRates = (decimal)dr["RoomRates"];
                    r.RoomStatus = (string)dr["RoomStatus"];
                    r.MaintainedBy = (string)dr["MaintainedBy"];
                    result.Add(r);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //Retrieve all Rooms By checkin and checkout date
        public static ArrayList retrieveAllRoomsByDate(DateTime checkin, DateTime checkout, string RoomType, string SmokingOrNot)
        {
            ArrayList result = new ArrayList();
            ArrayList ReservedRooms = retrieveAllReservedRooms(checkin, checkout);
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Rooms WHERE RoomType = @RoomType AND SmokingOrNot = @SmokingOrNot)";
                comm.Parameters.AddWithValue("@RoomType", RoomType);
                comm.Parameters.AddWithValue("@SmokingOrNot", SmokingOrNot);

                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process result
                while (dr.Read())
                {
                    Rooms r = new Rooms();
                    r.RoomNo = (int)dr["RoomNo"];
                    r.Floor = (int)dr["Floor"];
                    r.RoomType = (string)dr["RoomType"];
                    r.BedType = (string)dr["BedType"];
                    r.SmokingOrNot = (string)dr["SmokingOrNot"];
                    r.MaxOccupancy = (int)dr["MaxOccupancy"];
                    r.RoomRates = (decimal)dr["RoomRates"];
                    r.RoomStatus = "V";
                    foreach (Rooms room in ReservedRooms)
                    {
                        if ((int)dr["RoomNo"] == room.RoomNo)
                        {
                            r.RoomStatus = room.RoomStatus;
                        }
                    }
                    r.MaintainedBy = (string)dr["MaintainedBy"];
                    result.Add(r);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region RoomBooking DB Manager | Book Rooms for other Dates
        //Create New Room Booking
        public static int createNewBooking(RoomsBooking rb)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Invoice(BookingID,RoomNo,GuestID,StaffID,NoOfAdults,NoOfChild,DesiredCheckInDate,DesiredCheckOutDate,AdditionalReq,LateCheckOut,IsReserved) VALUES (@BookingID,@RoomNo,@GuestID,@StaffID,@NoOfAdults,@NoOfChild,@DesiredCheckInDate,@DesiredCheckOutDate,@AdditionalReq,@LateCheckOut,@IsReserved)";

                //3. Bind values to SQL Parameters
                comm.Parameters.AddWithValue("@BookingID", rb.BookingID);
                comm.Parameters.AddWithValue("@RoomNo", rb.RoomNo);
                comm.Parameters.AddWithValue("@GuestID", rb.GuestID);
                comm.Parameters.AddWithValue("@StaffID", rb.StaffID);
                comm.Parameters.AddWithValue("@NoOfAdults", rb.NoOfAdults);
                comm.Parameters.AddWithValue("@NoOfChild", rb.NoOfChild);
                comm.Parameters.AddWithValue("@DesiredCheckInDate", parseDateTimeToDBDate(rb.DesiredCheckInDate));
                comm.Parameters.AddWithValue("@DesiredCheckOutDate", parseDateTimeToDBDate(rb.DesiredCheckOutDate));
                comm.Parameters.AddWithValue("@AdditionalReq", rb.AdditionalReq);
                comm.Parameters.AddWithValue("@LateCheckOut", rb.LateCheckOut);
                comm.Parameters.AddWithValue("@IsReserved", rb.IsReserved);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        // Parse Datetime attribute to Date Attribute in DB
        private static string parseDateTimeToDBDate(DateTime datetime)
        {
            string date = datetime.ToString("yyyy-MM-dd");
            return date;
        }

        //Retrieve all Rooms that are Reserved
        public static ArrayList retrieveAllReservedRooms(DateTime checkin, DateTime checkout)
        {
            string cIn = parseDateTimeToDBDate(checkin);
            string cOut = parseDateTimeToDBDate(checkout);
            ArrayList result = new ArrayList();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT RoomNo FROM Rooms WHERE (RoomNo IN (SELECT RoomNo FROM RoomBooking WHERE (IsReserved = 'T') AND (DesiredCheckInDate BETWEEN @checkin AND @checkout) OR (DesiredCheckOutDate BETWEEN @checkin AND @checkout)))";
                comm.Parameters.AddWithValue("@checkin", checkin);
                comm.Parameters.AddWithValue("@checkout", checkout);

                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process result
                while (dr.Read())
                {
                    Rooms r = new Rooms();
                    r.RoomStatus = "R";
                    r.RoomNo = (int)dr["roomNo"];
                    result.Add(r);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //Retrieving All Room that are Occupied on a specific range of date!
        public static ArrayList retrieveAllRoomOccupied(string checkin, string checkout)
        {
            ArrayList result = new ArrayList();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT RoomNo FROM RoomBooking WHERE (BookingID IN (SELECT BookingID FROM Invoice WHERE (CheckInDateTime BETWEEN @checkin AND @checkout) OR (CheckOutDateTime BETWEEN @checkin AND @checkout)))";
                comm.Parameters.AddWithValue("@checkin", checkin);
                comm.Parameters.AddWithValue("@checkout", checkout);

                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process result
                while (dr.Read())
                {
                    Rooms r = new Rooms();
                    r.RoomStatus = "O";
                    r.RoomNo = (int)dr["roomNo"];
                    result.Add(r);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //Find BookingID
        public static bool searchBookingID(string id)
        {
            bool result = false;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM RoomBooking where BookingID = @BookingID";
                comm.Parameters.AddWithValue("@BookingID", id);

                //3. execute sql statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process
                if (dr.Read())
                {
                    result = true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //UpdateRoomBooking Records
        public static int updateRoomBookingbyID(RoomsBooking rb)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE RoomBooking SET RoomNo = @RoomNo, GuestID = @GuestID, StaffID = @StaffID, NoOfAdults = @NoOfAdults, NoOfChild = @NoOfChild, DesiredCheckInDate = @DesiredCheckInDate, DesiredCheckOutDate = @DesiredCheckOutDate, AdditionalReq = @AdditionalReq, LateCheckOut = @LateCheckOut, IsReserved = @IsReserved WHERE BookingID = @BookingID";

                //3. Bind values to SQL Parameters
                comm.Parameters.AddWithValue("@BookingID", rb.BookingID);
                comm.Parameters.AddWithValue("@RoomNo", rb.RoomNo);
                comm.Parameters.AddWithValue("@GuestID", rb.GuestID);
                comm.Parameters.AddWithValue("@StaffID", rb.StaffID);
                comm.Parameters.AddWithValue("@NoOfAdults", rb.NoOfAdults);
                comm.Parameters.AddWithValue("@NoOfChild", rb.NoOfChild);
                comm.Parameters.AddWithValue("@DesiredCheckInDate", parseDateTimeToDBDate(rb.DesiredCheckInDate));
                comm.Parameters.AddWithValue("@DesiredCheckOutDate", parseDateTimeToDBDate(rb.DesiredCheckOutDate));
                comm.Parameters.AddWithValue("@AdditionalReq", rb.AdditionalReq);
                comm.Parameters.AddWithValue("@LateCheckOut", rb.LateCheckOut);
                comm.Parameters.AddWithValue("@IsReserved",rb.IsReserved);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region Invoice DB Manager | Check - In for current room booking
        //Creating Invoice
        public static int createInvoice(Invoice inv)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Invoice(InvoiceID,BookingID,CheckInDateTime,CheckOutDateTime,NumberOfNights,PaymentMode,AdditionalCost,TotalCost) VALUES (@InvoiceID,@BookingID,@CheckInDateTime,@CheckOutDateTime,@NumberOfNights,@PaymentMode,@AdditionalCost,@TotalCost)";

                //3. Bind values to SQL Parameters
                comm.Parameters.AddWithValue("@InvoiceID", inv.InvoiceID);
                comm.Parameters.AddWithValue("@BookingID", inv.BookingID);
                comm.Parameters.AddWithValue("@CheckInDateTime",inv.CheckInDateTime);
                comm.Parameters.AddWithValue("@CheckOutDateTime", inv.CheckOutDateTime);
                comm.Parameters.AddWithValue("@NumberOfNights", inv.NumberOfNights);
                comm.Parameters.AddWithValue("@PaymentMode", inv.PaymentMode);
                comm.Parameters.AddWithValue("@AdditionalCost", inv.AdditionalCost);
                comm.Parameters.AddWithValue("@TotalCost", inv.TotalCost);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }
        
        //Retrieving Invoice
        public static Invoice retrieveInvoiceByID(string invoiceID)
        {
            Invoice result = new Invoice();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID";
                comm.Parameters.AddWithValue("@InvoiceID", invoiceID);

                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process result
                while (dr.Read())
                {
                    result.InvoiceID = (string)dr["InvoiceID"];
                    result.BookingID = (string)dr["BookingID"];
                    result.CheckInDateTime = (DateTime)dr["CheckInDateTime"];
                    result.CheckOutDateTime = (DateTime)dr["CheckOutDateTime"];
                    result.NumberOfNights = (int)dr["NumberOfNights"];
                    result.PaymentMode = (string)dr["PaymentMode"];
                    result.AdditionalCost = (decimal)dr["AdditionalCost"];
                    result.TotalCost = (decimal)dr["TotalCost"];
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //Search Invoice by ID
        public static bool searchInvoiceID(string invoiceID)
        {
            bool result = false;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Invoice where InvoiceID = @InvoiceID";
                comm.Parameters.AddWithValue("@InvoiceID", invoiceID);

                //3. execute sql statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process
                if (dr.Read())
                {
                    result = true;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }
        
        //Updating Invoice
        public static int updateInvoice(Invoice inv)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Invoice SET BookingID = @BookingID, CheckInDateTime = @CheckInDateTime, CheckOutDateTime = @CheckOutDateTime, NumberOfNights = @NumberOfNights, PaymentMode = @PaymentMode, AdditionalCost = @AdditionalCost, TotalCost = @TotalCost WHERE InvoiceID = @InvoiceID";


                //3. execute sql statement
                comm.Parameters.AddWithValue("@InvoiceID", inv.InvoiceID);
                comm.Parameters.AddWithValue("@BookingID", inv.BookingID);
                comm.Parameters.AddWithValue("@CheckInDateTime",parseDateTimeToDBDate(inv.CheckInDateTime));
                comm.Parameters.AddWithValue("@CheckOutDateTime",parseDateTimeToDBDate(inv.CheckOutDateTime));
                comm.Parameters.AddWithValue("@NumberOfNights", inv.NumberOfNights);
                comm.Parameters.AddWithValue("@PaymentMode", inv.PaymentMode);
                comm.Parameters.AddWithValue("@AdditionalCost", inv.AdditionalCost);
                comm.Parameters.AddWithValue("@TotalCost", inv.TotalCost);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        #endregion

        #region Guest DB Manager
        //Creating New Guest
        public static int createGuest(Guest g)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Invoice(GuestID,FirstName,LastName,PhoneNo,Email,HomeAddress,PostalCode,MailingAddress,Nationality) VALUES (@GuestID,@FirstName,@LastName,@PhoneNo,@Email,@HomeAddress,@PostalCode,@MailingAddress,@Nationality)";

                //3. Bind values to SQL Parameters
                comm.Parameters.AddWithValue("@GuestID", g.GuestID);
                comm.Parameters.AddWithValue("@FirstName", g.FirstName);
                comm.Parameters.AddWithValue("@LastName", g.LastName);
                comm.Parameters.AddWithValue("@PhoneNo", g.PhoneNo);
                comm.Parameters.AddWithValue("@Email", g.Email);
                comm.Parameters.AddWithValue("@HomeAddress", g.HomeAddress);
                comm.Parameters.AddWithValue("@PostalCode", g.PostalCode);
                comm.Parameters.AddWithValue("@MailingAddress", g.MailingAddress);
                comm.Parameters.AddWithValue("@Nationality", g.Nationality);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //Update Guest Info By GuestID
        public static int updateGuestInfo(Guest g)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Guest SET FirstName = @FirstName, LastName = @LastName, PhoneNo = @PhoneNo, Email = @Email, HomeAddress = @HomeAddress, PostalCode = @PostalCode, MailingAddress = @MailingAddress, Nationality = @Nationality  WHERE GuestID = @GuestID";


                //3. execute sql statement
                comm.Parameters.AddWithValue("@GuestID", g.GuestID);
                comm.Parameters.AddWithValue("@FirstName", g.FirstName);
                comm.Parameters.AddWithValue("@LastName", g.LastName);
                comm.Parameters.AddWithValue("@PhoneNo", g.PhoneNo);
                comm.Parameters.AddWithValue("@Email", g.Email);
                comm.Parameters.AddWithValue("@HomeAddress", g.HomeAddress);
                comm.Parameters.AddWithValue("@PostalCode", g.PostalCode);
                comm.Parameters.AddWithValue("@MailingAddress", g.MailingAddress);
                comm.Parameters.AddWithValue("@Nationality",g.Nationality);

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        //Credit Card DB Manager
        //Create New Credit Card Detail
        public static int createCreditCardDetail(CreditCard c)
        {
            int result = 0;
            try
            {
                //1. Connect to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CreditCard(ccNo,CardholderName,GuestID,CardType,CardExpiryDate) VALUES (@ccNo,@CardholderName,@GuestID,@CardType,@CardExpiryDate)";

                //3. Bind values to SQL Parameters
                comm.Parameters.AddWithValue("@ccNo", c.CCNo);
                comm.Parameters.AddWithValue("@CardholderName", c.CardholderName);
                comm.Parameters.AddWithValue("@GuestID", c.GuestID);
                comm.Parameters.AddWithValue("@CardType", c.CardType);
                comm.Parameters.AddWithValue("@CardExpiryDate",parseDateTimeToDBDate(c.CardExpiryDate));

                //4. Execute SQL Statement
                result = comm.ExecuteNonQuery();

                //5. Close connection
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #endregion

        #region Housekeeping Management Module Start | Staff & Duties
        //CreateNewStaff
        public static int createNewStaff(StaffProfile m)
        {
            int result = 0;
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO StaffProfile(StaffID,FirstName,LastName,DOB,HomeAddress,PhoneNo,BankAccNo,DutiesID) VALUES (@StaffID,@firstName,@lastName,@DOB,@homeAddress,@phoneNo,@bankAccNo,@DutiesID)";

                comm.Parameters.AddWithValue("@StaffID", m.StaffID);
                comm.Parameters.AddWithValue("@firstName", m.FirstName);
                comm.Parameters.AddWithValue("@lastName", m.LastName);
                comm.Parameters.AddWithValue("@DOB",parseDateTimeToDBDate(m.DOB1));
                comm.Parameters.AddWithValue("@homeAddress", m.HomeAddress);
                comm.Parameters.AddWithValue("@phoneNo", m.PhoneNo);
                comm.Parameters.AddWithValue("@bankAccNo", m.BankAccNo);
                comm.Parameters.AddWithValue("@DutiesID", m.DutiesID1);

                result = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static StaffProfile retrieveStaff(string StaffID)
        {
            StaffProfile result = new StaffProfile();
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM StaffProfile where StaffID = @StaffID";
                comm.Parameters.AddWithValue("@StaffID", StaffID);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    result.StaffID = (string)dr["StaffID"];
                    result.FirstName = (string)dr["firstName"];
                    result.LastName = (string)dr["lastName"];
                    result.DOB1 = (DateTime)dr["DOB"];
                    result.HomeAddress = (string)dr["homeAddress"];
                    result.PhoneNo = (int)dr["phoneNo"];
                    result.BankAccNo = (string)dr["bankAccNo"];
                    result.DutiesID1 = (string)dr["DutiesID"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList retrieveAllStaff()
        {
            ArrayList result = new ArrayList();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * StaffProfile";
                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();
                //4. Fetch and process result
                while (dr.Read())
                {
                    StaffProfile staff = new StaffProfile();

                    staff.StaffID = (string)dr["StaffID"];
                    staff.FirstName = (string)dr["firstName"];
                    staff.LastName = (string)dr["lastName"];
                    staff.DOB1 = (DateTime)dr["DOB"];
                    staff.HomeAddress = (string)dr["homeAddress"];
                    staff.PhoneNo = (int)dr["phoneNo"];
                    staff.BankAccNo = (string)dr["bankAccNo"];
                    staff.DutiesID1 = (string)dr["dutyID"];
                    
                    result.Add(staff);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }


        public static int updateStaffProfileByStaffID(StaffProfile m)
        {
            int rowUpdated = 0;
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE StaffProfile SET FirstName= @firstName,LastName= @lastName,DOB= @DOB,HomeAddress= @homeAddress,PhoneNo= @phoneNo,BankAccNo= @bankAccNo,DutiesID= @DutiesID where StaffID= @StaffID";

                comm.Parameters.AddWithValue("@firstName", m.FirstName);
                comm.Parameters.AddWithValue("@lastName", m.LastName);
                comm.Parameters.AddWithValue("@DOB", m.DOB1);
                comm.Parameters.AddWithValue("@homeAddress", m.HomeAddress);
                comm.Parameters.AddWithValue("@phoneNo", m.PhoneNo);
                comm.Parameters.AddWithValue("@bankAccNo", m.BankAccNo);
                comm.Parameters.AddWithValue("@DutiesID", m.DutiesID1);
                comm.Parameters.AddWithValue("@StaffID", m.StaffID);

                rowUpdated = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowUpdated;
        }

        public static int deleteStaffProfile(StaffProfile m)
        {
            int rowDeleted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE INTO StaffProfile where StaffID = @StaffID";

                comm.Parameters.AddWithValue("@StaffID", m.StaffID);

                rowDeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowDeleted;
        }

        //Duties DB Manager
        //Create New Duties
        public static int createNewDuties(Duties m)
        {
            int result = 0;
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Duties(DutiesID,DutyName,StartPeriod,EndPeriod,Description) VALUES (@dutiesID,@dutyName,@startPeriod,@endPeriod,@description)";

                comm.Parameters.AddWithValue("@dutiesID", m.DutiesID);
                comm.Parameters.AddWithValue("@dutyName", m.DutyName);
                comm.Parameters.AddWithValue("@startPeriod",parseDateTimeToDBDate(m.StartPeriod));
                comm.Parameters.AddWithValue("@endPeriod",parseDateTimeToDBDate(m.EndPeriod));
                comm.Parameters.AddWithValue("@description", m.Description);

                result = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool searchDutiesByDutiesID(string DutiesID)
        {
            string result = "";
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Duties where DutiesID = @dutiesID";
                comm.Parameters.AddWithValue("@dutiesID", DutiesID);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    result = (string)dr["DutiesID"];
                }

                if (DutiesID == result)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        public static int updateDutiesByDutiesID(Duties m)
        {
            int rowUpdated = 0;
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Duties SET DutyName= @dutyName, StartPeriod= @startPeriod, EndPeriod= @endPeriod, Description= @description where DutiesID= @dutiesID";

                comm.Parameters.AddWithValue("@dutiesID", m.DutiesID);
                comm.Parameters.AddWithValue("@dutyName", m.DutyName);
                comm.Parameters.AddWithValue("@startPeriod", m.StartPeriod);
                comm.Parameters.AddWithValue("@endPeriod", m.EndPeriod);
                comm.Parameters.AddWithValue("@description", m.Description);

                rowUpdated = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowUpdated;
        }

        public static int deleteDuties(Duties m)
        {
            int rowDeleted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE INTO Duties where DutiesID = @dutiesID";

                comm.Parameters.AddWithValue("@dutiesID", m.DutiesID);

                rowDeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowDeleted;
        }
        #endregion

        #region Reporting Module Starts | 5 reporting module
        //Room status report
        public static ArrayList retrieveAllRoomStatus()
        {
            ArrayList result = new ArrayList();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomNo,RoomStatus FROM Rooms";

                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();

                //4. Fetch and process result
                while (dr.Read())
                {
                    Rooms r = new Rooms();
                    r.RoomNo = (int)dr["roomNo"];
                    r.RoomStatus = (string)dr["RoomStatus"];
                    result.Add(r);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        // Room Occupancy Report
        public static List<Rooms> retrieveRooms(string roomType)
        {
            List<Rooms> result = new List<Rooms>();
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT RoomType FROM Rooms";
                comm.Parameters.AddWithValue("@roomType", roomType);

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Rooms room = new Rooms();
                    room.RoomNo = (int)dr["RoomNo"];
                    room.Floor = (int)dr["Floor"];
                    room.RoomType = (string)dr["RoomType"];
                    room.BedType = (string)dr["BedType"];
                    room.SmokingOrNot = (string)dr["SmokingOrNot"];
                    room.MaxOccupancy = (int)dr["MaxOccupancy"];
                    room.RoomRates = (decimal)dr["RoomRates"];
                    room.RoomStatus = (string)dr["RoomStatus"];
                    room.MaintainedBy = (string)dr["MaintainedBy"];

                    result.Add (room);
                    

        
                }

            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
      

        //List all guest that are in the particular room 
        public static Listallguestthatareintheroom getAllguest(string guestID)
        {
            Listallguestthatareintheroom result = new Listallguestthatareintheroom();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT g.FirstName, g.LastName, rb.CheckInDateTime, rb.DesiredCheckOutDateTime, rb.NoOfAdults, rb.NoOfChild from Guest g INNER JOIN RoomBooking rb ON  g.GuestID = rb.GuestID Where g.GuestID = @GuestID";
                comm.Parameters.AddWithValue("guestID",guestID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Listallguestthatareintheroom l = new Listallguestthatareintheroom();
                    l.FirstName = (string)dr["FirstName"];
                    l.LastName = (string)dr["LastName"];
                    l.CheckInDateTime = (DateTime)dr["CheckInDateTime"];
                    l.DesiredCheckOutDateTime = (DateTime)dr["DesiredCheckOutDateTime"];
                    l.NoOfAdults = (int)dr["NoOfAdults"];
                    l.NoOfChild = (int)dr["NoOfChild"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }

        //List all guest from all rooms staying in a specific day
        public static RoomsBooking retrieveGuestDetails(DateTime checkInDateTime)
        {
            RoomsBooking result = new RoomsBooking();
            try
            {
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM RoomBooking where CheckInDateTime = @CheckInDateTime";
                comm.Parameters.AddWithValue("@checkInDateTime", checkInDateTime);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {

                    result.BookingID = (string)dr["BookingID"];
                    result.GuestID = (string)dr["GuestID"];
                    result.RoomNo = (int)dr["RoomNo"];
                    result.StaffID = (string)dr["StaffID"];
                    result.NoOfAdults = (int)dr["NoOfAdults"];
                    result.NoOfChild = (int)dr["NoOfChild"];
                    result.DesiredCheckInDate = (DateTime)dr["DesiredCheckInDate"];
                    result.DesiredCheckOutDate = (DateTime)dr["DesiredCheckOutDate"];
                    result.AdditionalReq = (string)dr["AdditionalReq"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        // Housekeeping report
        public static List<housekeepingreport> getAllDuties(string duties)
        {
            List<housekeepingreport> result = new List<housekeepingreport>();

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT s.FirstName,s.LastName,d.DutyName,d.StartPeriod,d.EndPeriod,d.Description FROM StaffProfile s INNER JOIN Duties d ON s.DutiesID = d.DutiesID Where s.DutiesID = @DutiesID  ";
                comm.Parameters.AddWithValue("dutiesID", duties);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeepingreport h = new housekeepingreport();
                    h.FirstName = (string)dr["FirstName"];
                    h.LastName = (string)dr["LastName"];
                    h.DutyName = (string)dr["DutyName"];
                    h.StartPeriod = (DateTime)dr["StartPeriod"];
                    h.EndPeriod = (DateTime)dr["EndPeriod"];
                    h.Description = (string)dr["Description"];
                }
                dr.Close();
                conn.Close();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;

        }
        public static List<Duties> retrieveAllDutiesNames()
        {
            List<Duties> result = new List<Duties>();
            try
            {
                //1. Connection to the database
                SqlConnection conn = new SqlConnection();

                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["DRHotelDBConnectionString"].ConnectionString;

                conn.Open();
                //2. Prepare SQL statement
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT dutiesID FROM Duties";
                //3. Execute SQL statement
                SqlDataReader dr = comm.ExecuteReader();
                //4. Fetch and process result
                while (dr.Read())
                {
                    Duties duty = new Duties();

                    duty.DutiesID = (string)dr["DutiesID"];

                    result.Add(duty);
                }
                //5. Close the connections
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
    }
}